package app

import (
	"encoding/csv"
	"fmt"
	"io"
	"os"
	"strings"
	"time"
	"unicode"

	"git.sr.ht/~mil/mobsql/logger"
	"git.sr.ht/~mil/mobsql/sqlgen"
	"git.sr.ht/~mil/mobsql/utilfiles"
	"git.sr.ht/~mil/mobsql/utilfuncs"
)

// importTableFromSpec imports the given table from the tableSpec - reading
// the associated CSV file from the filesystem and inserting into the
// tableSpec's associated table name. Old entries marked with the table/source
// combination are deleted (before new row insertions).
//
// Prior to insertion, the checksum of the last insertion for the source
// table combination (is checked against the internal _mobsql tracking
// table).

// Upon succesful completion, the checksum, tablename, and source
// are stored in the internal tracking _mobql table. Resultingly, running
// this function with the same parameters twice will simply nop the second
// call (assuming the source CSV file is unchanged).
func (m *MDB) importTableFromSpec(loadTable *sqlgen.LoadTable, extraValues map[string]any, fromDirectory string, sourceName int) error {
	logger.Warnf(
		"Importing table from %s/%s (%0.02fMB) to table: %s",
		fromDirectory,
		loadTable.File,
		utilfiles.FileSizeMB(fromDirectory+"/"+loadTable.File),
		loadTable.TableSpec.Table,
	)
	if !utilfiles.FileExists(fromDirectory + "/" + loadTable.File) {
		logger.Warnf("Skipped import for non-existant non-required file: %s", fromDirectory+"/"+loadTable.File)
		return nil
	}
	defer logger.Warnf("Imported table from %s/%s to table: %s ok", fromDirectory, loadTable.File, loadTable.TableSpec.Table)

	shaSum := utilfiles.FileChecksum(fromDirectory + "/" + loadTable.File)

	if lastShasum, err := m.QueryValue("select checksum from _mobsql where source = ? and tbl = ?", []any{sourceName, loadTable.TableSpec.Table}); err != nil {
		return fmt.Errorf("Issue querying checksum for _mobsql table: %v", err)
	} else if lastShasum == shaSum {
		logger.Warnf("Cache skipped import for table %s and source %d matched existing checksum: %s", loadTable.TableSpec.Table, sourceName, shaSum)
		return nil
	} else if _, err := m.DB.Exec(fmt.Sprintf(`delete from %s where source = ?`, loadTable.TableSpec.Table), sourceName); err != nil && sourceName != -1 {
		return err
	} else {
		logger.Warnf("Last checksum vs current: %s / %s; import will occur", lastShasum, shaSum)
		if nRows, err := m.importLoadTableCSVToDBFromDirectory(loadTable, extraValues, fromDirectory, sourceName); err != nil {
			return err
		} else if _, err := m.DB.Exec(
			`insert into _mobsql (source, tbl, checksum, n_rows, timestamp) values(?, ?, ?, ?, ?)`,
			sourceName, loadTable.TableSpec.Table, shaSum, nRows, time.Now().Unix(),
		); err != nil {
			return err
		} else {
			logger.Warnf("Imported %d rows into %s ok", nRows, loadTable.TableSpec.Table)
		}
	}

	return nil
}

func (m *MDB) importLoadTableCSVToDBFromDirectory(loadTable *sqlgen.LoadTable, extraValues map[string]any, fromDirectory string, sourceName int) (int, error) {
	csvFile, err := os.Open(fromDirectory + "/" + loadTable.File)
	if err != nil {
		return 0, fmt.Errorf("Problem opening source file %s: %v", loadTable.File, err)
	}

	var (
		csvReader        = csv.NewReader(csvFile)
		csvColumnsHeader = []string{}
		rowBatch         [][]string
		batchInsertSize  = 2000
		rowI             = 0
	)

	defer csvFile.Close()
	if csvColumnsEntry, err := csvReader.Read(); err != nil {
		return 0, fmt.Errorf("Failed to read first line/headers of CSV: %v", err)
	} else {
		for _, header := range csvColumnsEntry {
			csvColumnsHeader = append(
				csvColumnsHeader,
				strings.TrimFunc(header, func(c rune) bool {
					return !unicode.IsGraphic(c)
				}),
			)
		}
	}
	for {
		if csvColumnsEntry, err := csvReader.Read(); err == io.EOF {
			break
		} else if err != nil {
			return 0, err
		} else {
			rowI += 1
			rowBatch = append(rowBatch, csvColumnsEntry)
			if len(rowBatch) == batchInsertSize {
				if err := m.insert(&loadTable.TableSpec, loadTable.TableSpec.Columns, csvColumnsHeader, rowBatch, extraValues); err != nil {
					return 0, fmt.Errorf("Problem inserting: %v", err)
				}
				rowBatch = [][]string{}
			}
		}
	}
	if len(rowBatch) > 0 {
		if err := m.insert(&loadTable.TableSpec, loadTable.TableSpec.Columns, csvColumnsHeader, rowBatch, extraValues); err != nil {
			return 0, fmt.Errorf("Problem inserting: %v", err)
		}
	}
	return rowI, nil
}

func (m *MDB) insert(
	tableSpec *sqlgen.TableSpec,
	loadColumns []sqlgen.LoadColumn,
	csvColumnsHeader []string,
	csvColumnsEntries [][]string,
	extraValues map[string]any,
) error {
	insertMaps := []map[string]any{}
	for _, entryColumns := range csvColumnsEntries {
		add := map[string]any{}
		for k, v := range extraValues {
			add[k] = v
		}
		for i, key := range csvColumnsHeader {
			add[key] = entryColumns[i]
		}
		insertMaps = append(insertMaps, add)
	}

	columnsToLoad := utilfuncs.MapKeys(extraValues)
	for _, loadColumn := range loadColumns {
		// Skip non-required fields that are missing
		if !loadColumn.Required && !utilfuncs.ItemInArray(loadColumn.Column, csvColumnsHeader) {
			continue
		}

		// Perform conversions where specified
		if loadColumn.Conversion != nil {
			for i, _ := range insertMaps {
				if converted, err := loadColumn.Conversion(insertMaps[i][loadColumn.Column].(string)); err != nil {
					logger.Warnf("Skipped import for row: %v due to failed conversion: %v", insertMaps[i], err)
					continue
				} else {
					insertMaps[i][loadColumn.Column] = converted
				}
			}
		}

		// Use NULL rather then empty string ("") for non required fields if found
		// TODO: There may be cases where this functionality is not ideal, maybe...
		//       eventually implement LoadColumn InsertAsNullIfEmpty / InsertAsEmptyStringRatherThanNull
		if !loadColumn.Required {
			for i, _ := range insertMaps {
				if insertMaps[i][loadColumn.Column] == "" {
					insertMaps[i][loadColumn.Column] = nil
				}
			}
		}

		columnsToLoad = append(columnsToLoad, loadColumn.Column)
	}

	colonColumns := strings.Join(utilfuncs.ColonPrefixItems(columnsToLoad), ",")
	for i, _ := range columnsToLoad {
		columnsToLoad[i] = fmt.Sprintf("'%s'", columnsToLoad[i])
	}

	insertStatement := fmt.Sprintf(
		`insert into %s (%s) values(%s)`,
		tableSpec.Table,
		strings.Join(columnsToLoad, ","),
		colonColumns,
	)

	if _, err := m.DB.NamedExec(insertStatement, insertMaps); err != nil {
		return fmt.Errorf("Error with insert statement: %s\n  with maps: %v\n  error: %v", insertStatement, insertMaps, err)
	} else {
		return nil
	}
}
